﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.Common;


namespace Shohdi.DataHandler
{
    public class ClassDataHandler
    {
        #region PrivateVars
        /// <summary>
        /// this variable will be used when you open a transaction
        /// </summary>
        protected DbTransaction sqlTrans = null;


        /// <summary>
        /// this string will have the provider to work with , this provider will be used to create objects
        /// </summary>
        protected string dataProvider = "System.Data.SqlClient";

        /// <summary>
        /// this variable will hold the connection string
        /// </summary>
        protected string _strConnection;

        /// <summary>
        /// this variable will hold the connection created by _strConnections
        /// </summary>
        protected DbConnection _sqlConnection;
        #endregion

        #region PublicVars

        #endregion

        #region privateMethods
        
        /// <summary>
        /// this function will take a connection object and open it if it's not opened
        /// </summary>
        /// <param name="objSqlConn">this is the connection object that will be opened</param>
        protected void OpenConnection(DbConnection objSqlConn)
        {
            if (objSqlConn.State != ConnectionState.Open) objSqlConn.Open();
        }

        /// <summary>
        /// this function for closing connection
        /// </summary>
        /// <param name="objSqlConn">this is the connection object to close</param>
        protected void CloseConnection(DbConnection objSqlConn)
        {
            if (objSqlConn.State == ConnectionState.Open) objSqlConn.Close();
        }

        /// <summary>
        /// this function will create a sql connection and return it
        /// </summary>
        /// <returns>the created connection</returns>
        protected DbConnection GetOpenConnection()
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);

            DbConnection sqlConn = dpf.CreateConnection();
            try
            {
                sqlConn.ConnectionString = _strConnection;
                if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
            }
            catch (Exception ex)
            {
                ErrorHandle(ex);
            }
            return sqlConn;
        }
        #endregion privatemethods

        #region public Constractors


        /// <summary>
        /// the constractor , this will take a connection string to build object with it
        /// </summary>
        /// <param name="strConnectionString">the connection string</param>
        public ClassDataHandler(string strConnectionString,params string [] provider)
        {
            if (provider.Length > 0)
            {
                dataProvider = provider[0];
            }
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            _strConnection = strConnectionString;
            _sqlConnection =dpf.CreateConnection();
            _sqlConnection.ConnectionString = _strConnection;
        }
        //public ClassDataHandler()
        //{
        //}
        #endregion Constractors

        #region PublicMethods

        /// <summary>
        /// this function will open the main connection
        /// </summary>
        public void OpenConnection()
        {
            if (_sqlConnection.State != ConnectionState.Open) _sqlConnection.Open();
        }


        /// <summary>
        /// this funtion will close the main connection
        /// </summary>
        public  void CloseConnection()
        {
            if (_sqlConnection.State == ConnectionState.Open) _sqlConnection.Close();
        }


        /// <summary>
        /// this function will open the main connection and attach a transaction with it
        /// </summary>
        /// <param name="iso">the transaction level (read etc ....)</param>
        public void OpenTransConnection(params IsolationLevel[] iso)
        {
            
            
            OpenConnection();
            if (iso.Length > 0)
                sqlTrans = _sqlConnection.BeginTransaction (iso[0]);
            else
                sqlTrans = _sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            
            
        }

        
        /// <summary>
        /// this function will commit the transaction
        /// </summary>
        public void CommitTransConnection()
        {
            sqlTrans.Commit();
            CloseConnection();
            sqlTrans = null;
        }


        /// <summary>
        /// this function for rolling back the transaction and discard any changes to the database
        /// </summary>
        public void RollbackTransConnection()
        {
            sqlTrans.Rollback();
            CloseConnection();
            sqlTrans = null;
        }


        /// <summary>
        /// this will return the connection string of the main connection
        /// </summary>
        public string strConnection
        {
            get
            {
                return _strConnection;
            }
        }


        /// <summary>
        /// this function will test the main connection return true if it's ok
        /// </summary>
        /// <returns>true for ok , false for wrong connection</returns>
        public bool CheckConnection()
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            bool bolConnectionFlag;
            DbConnection sqlConn = dpf.CreateConnection();
            try
            {
                sqlConn.ConnectionString = _strConnection;
                sqlConn.Open();
                bolConnectionFlag = true;
            }
            catch
            {
                bolConnectionFlag = false;
            }
            finally
            {
                CloseConnection(sqlConn);
            }
            return bolConnectionFlag;
        }



        /// <summary>
        /// this function will get the date from the server ( Sql server only , Will not work with other providers )
        /// </summary>
        /// <returns>the returned date</returns>
        public DateTime GetServerDate()
        {
            return (DateTime)ExecuteScalar("SELECT GETDATE()", CommandType.Text);
        }



        /// <summary>
        /// this function will select a schema from the database and return it in a datatable
        /// </summary>
        /// <param name="strSqlCmd">the query string which will executed on the database</param>
        /// <param name="ctCmdType">the type of the command (text , stored ...)</param>
        /// <param name="prmSqlParameter">the parameters in the query (optional)</param>
        /// <returns>a datatable with the returned data</returns>
        public DataTable SelectSchemaInDataTable(string strSqlCmd, CommandType ctCmdType, params DbParameter[] prmSqlParameter)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            DataTable dtTemp = new DataTable();
            if (this._sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            //SqlConnection sqlConn = GetOpenConnection();
            try
            {
                DbCommand sqlCmd = dpf.CreateCommand();
                sqlCmd.CommandText = strSqlCmd;
                sqlCmd.Connection = _sqlConnection;

                if (sqlTrans != null) sqlCmd.Transaction = sqlTrans;
                sqlCmd.CommandType = ctCmdType;
                if (prmSqlParameter.Length > 0)
                {
                    DbParameter[] sprm = CopyArrayParameter(prmSqlParameter);
                    sqlCmd.Parameters.AddRange(sprm);

                }
                DbDataAdapter sqlAdapt = dpf.CreateDataAdapter();
                sqlAdapt.SelectCommand = sqlCmd;
                sqlAdapt.FillSchema(dtTemp,SchemaType.Mapped);
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                {
                    ErrorHandle(ex);
                    throw new Exception("Data Error.");
                }
                else
                {
                    ErrorHandle(ex);
                }
            }
            finally
            {
                //CloseConnection(sqlConn);
            }
            return dtTemp;
        }





        /// <summary>
        /// this function will select a data from the database and return it in a datatable
        /// </summary>
        /// <param name="strSqlCmd">the query string which will executed on the database</param>
        /// <param name="ctCmdType">the type of the command (text , stored ...)</param>
        /// <param name="prmSqlParameter">the parameters in the query (optional)</param>
        /// <returns>a datatable with the returned data</returns>
        public DataTable SelectInDataTable(string strSqlCmd, CommandType ctCmdType, params DbParameter[] prmSqlParameter)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            DataTable dtTemp = new DataTable();
            if (this._sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            //SqlConnection sqlConn = GetOpenConnection();
            try
            {
                DbCommand sqlCmd = dpf.CreateCommand();
                sqlCmd.CommandText = strSqlCmd;
                sqlCmd.Connection = _sqlConnection;
                                   
                if (sqlTrans != null) sqlCmd.Transaction = sqlTrans;
                sqlCmd.CommandType = ctCmdType;
                if (prmSqlParameter.Length > 0)
                {
                    DbParameter[] sprm = CopyArrayParameter(prmSqlParameter);
                    sqlCmd.Parameters.AddRange(sprm);

                }
                DbDataAdapter sqlAdapt = dpf.CreateDataAdapter();
                sqlAdapt.SelectCommand = sqlCmd;
                sqlAdapt.Fill(dtTemp);
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                {
                    ErrorHandle(ex);
                    throw new Exception("Data Error.");
                }
                else
                {
                    ErrorHandle(ex);
                }
            }
            finally
            {
                //CloseConnection(sqlConn);
            }
            return dtTemp;
        }



        /// <summary>
        /// this function will select a data from the database and return it in a sqldatareader
        /// </summary>
        /// <param name="strSqlCmd">the query string which will executed on the database</param>
        /// <param name="ctCmdType">the type of the command (text , stored ...)</param>
        /// <param name="prmSqlParameter">the parameters in the query (optional)</param>
        /// <returns>a sqldatareader with the returned data</returns>
        public DbDataReader  SelectDataReader (string strSqlCmd, CommandType ctCmdType, params DbParameter[] prmSqlParameter)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            DbDataReader drTemp = null;
            if (this._sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            //SqlConnection sqlConn = GetOpenConnection();
            try
            {
                DbCommand sqlCmd = dpf.CreateCommand();
                sqlCmd.CommandText = strSqlCmd;
                sqlCmd.Connection = _sqlConnection;

                
                if (sqlTrans != null) sqlCmd.Transaction = sqlTrans;
                sqlCmd.CommandType = ctCmdType;
                if (prmSqlParameter.Length > 0)
                {
                    DbParameter[] sprm = CopyArrayParameter(prmSqlParameter);
                    sqlCmd.Parameters.AddRange(sprm);

                }

                drTemp = sqlCmd.ExecuteReader();

            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                {
                    ErrorHandle(ex);
                    throw new Exception("Data Error.");
                }
                else
                {
                    ErrorHandle(ex);
                }
            }
            finally
            {
                //CloseConnection(sqlConn);
            }
            return drTemp;
        }





        /// <summary>
        /// this function will execute a query on the database
        /// </summary>
        /// <param name="strSqlCmd">the query string which will executed on the database</param>
        /// <param name="ctCmdType">the type of the command (text , stored ...)</param>
        /// <param name="prmSqlParameter">the parameters in the query (optional)</param>
        public bool ExecuteNonQuery(string strSqlCmd, CommandType ctCmdType, params DbParameter[] prmSqlParameter)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            if (this._sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            //SqlConnection sqlConn = GetOpenConnection();
            try
            {
                DbCommand sqlCmd = dpf.CreateCommand();
                sqlCmd.CommandText = strSqlCmd;
                sqlCmd.Connection = _sqlConnection;

                
                if (sqlTrans != null) sqlCmd.Transaction = sqlTrans;
                sqlCmd.CommandType = ctCmdType;
                if (prmSqlParameter.Length > 0)
                {
                    DbParameter[] sprm = CopyArrayParameter(prmSqlParameter);
                    sqlCmd.Parameters.AddRange(sprm);

                }
                int nCount = sqlCmd.ExecuteNonQuery();
                //File.AppendAllText ("c:\\count.txt","\r\n" + nCount.ToString() + " " + ((prmSqlParameter.Length > 0)?prmSqlParameter[0].Value.ToString():""));
                return true;
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                {
                    ErrorHandle(ex);
                    throw new Exception("Data Error.");
                }
                else
                {
                    ErrorHandle(ex);
                    return false;
                }
            }
            finally
            {
                //CloseConnection(sqlConn);
            }
        }



        /// <summary>
        /// this function will select a value from the database and return an object holding this value
        /// </summary>
        /// <param name="strSqlCmd">the query string which will executed on the database</param>
        /// <param name="ctCmdType">the type of the command (text , stored ...)</param>
        /// <param name="prmSqlParameter">the parameters in the query (optional)</param>
        /// <returns>an object with the returned value</returns>
        public object ExecuteScalar(string strSqlCmd, CommandType ctCmdType, params DbParameter[] prmSqlParameter)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            object objRet = new object();
            if (this._sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            //SqlConnection sqlConn = GetOpenConnection();

            try
            {
                DbCommand sqlCmd = dpf.CreateCommand();
                sqlCmd.CommandText = strSqlCmd;
                sqlCmd.Connection = _sqlConnection;
                   
                if (sqlTrans != null) sqlCmd.Transaction = sqlTrans;
                sqlCmd.CommandType = ctCmdType;
                if (prmSqlParameter.Length > 0)
                {
                    DbParameter[] sprm = CopyArrayParameter(prmSqlParameter);
                    sqlCmd.Parameters.AddRange(sprm);

                }
                objRet = sqlCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                {
                    ErrorHandle(ex);
                    throw new Exception("Data Error.");
                }
                else
                {
                    ErrorHandle(ex);
                }
            }
            //CloseConnection(sqlConn);
            return objRet;
        }


        /// <summary>
        /// this function will filter datatable and return the result in a new datatable
        /// </summary>
        /// <param name="dtIn">the datatable to filter</param>
        /// <param name="strFilter">the filter string</param>
        /// <returns>the returned datatable</returns>
        public static DataTable DTFilterTable(DataTable dtIn, string strFilter)
        {
            try
            {
                DataView dv = new DataView(dtIn, strFilter, "", DataViewRowState.CurrentRows);
                return dv.ToTable();
            }
            catch (Exception ex)
            {
                ErrorHandle(ex);
                DataTable dt = new DataTable();
                dt = dtIn.Copy();
                dt.Rows.Clear();
                return dtIn;

            }
        }



        /// <summary>
        /// this function will make a union on two datatables
        /// </summary>
        /// <param name="dtIn">the first datatable</param>
        /// <param name="dtAppended">the secound datatable</param>
        /// <returns>the result datatable with the data in the two tables</returns>
        public static DataTable AppendTable(DataTable dtIn, DataTable dtAppended)
        {
            DataTable dtRet = new DataTable();
            try
            {
                dtRet = dtIn.Copy();
                if (dtRet.Rows.Count == 0)
                {
                    dtRet = dtAppended.Copy();
                    return dtRet;
                }
                for (int i = 0; i < dtAppended.Rows.Count; i++)
                {
                    dtRet.Rows.Add(dtRet.NewRow());
                    object[] oIn = new object[dtAppended.Columns.Count];
                    dtAppended.Rows[i].ItemArray.CopyTo(oIn, 0);
                    dtRet.Rows[dtRet.Rows.Count - 1].ItemArray = oIn;
                }
            }
            catch (Exception ex)
            {
                ErrorHandle(ex);
            }
            return dtRet;
        }


        /// <summary>
        /// this function will store the exception in an error log file
        /// </summary>
        /// <param name="exin">the exception</param>
        public static void ErrorHandle(Exception exin)
        {
        //    try
        //    {
        //        System.Threading.Semaphore sm = new System.Threading.Semaphore(1, 1, Application.StartupPath.Replace('\\', '_').Replace(':', '_') + @"_Error.log");
        //        //System.Windows.Forms.MessageBox.Show(sm.ToString());
        //        //sm.WaitOne();
        //        if (System.IO.File.Exists(Application.StartupPath + @"\Error.log"))
        //        {
        //            System.IO.FileInfo fi = new System.IO.FileInfo(Application.StartupPath + @"\Error.log");
        //            if (fi.Length > 20000) fi.Delete();
        //        }
        //        System.IO.File.AppendAllText(Application.StartupPath + @"\Error.log", "\r\n" + DateTime.Now.ToString() + "\r\n" + exin.ToString());
        //        //sm.Release();
        //    }
        //    catch (Exception ex)
        //    {
        //        //System.Windows.Forms.MessageBox.Show(ex.ToString() + "\r\n" + exin.ToString());
        //    }
        }



        



        


        /// <summary>
        /// this function will get the index of the first row having the value
        /// </summary>
        /// <param name="dtIn">the datatable which will be searched in</param>
        /// <param name="strColumnName">the name of the column to search in</param>
        /// <param name="oValue">the value which will search by</param>
        /// <returns>the index of the first row having the searched value</returns>
        public static int GetIndexByID(DataTable dtIn, string strColumnName, object oValue)
        {
            int i = 0;
            for (i = 0; i < dtIn.Rows.Count; i++)
                if (dtIn.Rows[i][strColumnName].ToString().ToLower() == oValue.ToString().ToLower())
                    break;
            if (i == dtIn.Rows.Count) return -1;
            return i;
        }


        /// <summary>
        /// this function will copy an array of sqlparameter to another one ( this because if you used a sqlparameter in a query you cannot use it again )
        /// </summary>
        /// <param name="prmSqlParameter">the sqlparameter array to copy</param>
        /// <returns>the new sqlparameter array</returns>
        public DbParameter[] CopyArrayParameter(DbParameter[] prmSqlParameter)
        {
            List<DbParameter> sprm = new List<DbParameter>();
            
            for (int i = 0; i < prmSqlParameter.Length; i++)
            {
                sprm.Add(CopyParameter(prmSqlParameter[i]));

            }
            return sprm.ToArray();
        }

        /// <summary>
        /// this function will copy one sqlparameter to a new sqlparameter
        /// </summary>
        /// <param name="prm">the parameter to copy</param>
        /// <returns>the new sqlparameter</returns>
        public DbParameter CopyParameter(DbParameter prm)
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            DbParameter sRet = dpf.CreateParameter();
            

            

            sRet.DbType = prm.DbType;
            sRet.Direction = prm.Direction;
            sRet.IsNullable = prm.IsNullable;
            
            sRet.ParameterName = prm.ParameterName;
            
            sRet.Size = prm.Size;
            sRet.SourceColumn = prm.SourceColumn;
            sRet.SourceColumnNullMapping = prm.SourceColumnNullMapping;
            sRet.SourceVersion = prm.SourceVersion;

            sRet.DbType = prm.DbType;
            sRet.Value = prm.Value;
          

            
            return sRet;
        }



        /// <summary>
        /// This function will create an empty parameter and return it
        /// </summary>
        /// <returns>The parameter created by the function</returns>
        public DbParameter createParameter()
        {
            DbProviderFactory dpf = DbProviderFactories.GetFactory(dataProvider);
            return dpf.CreateParameter();
        }


        /// <summary>
        /// this function will copy one datarow item array to a new array to create a new datarow
        /// </summary>
        /// <param name="arr">the array to copy</param>
        /// <returns>the new array</returns>
        public static object[] copyRowArray(object[] arr)
        {
            object[] oRet = new object[arr.Length];

            for (int i = 0; i < arr.Length; i++)
            {
                if (arr[i] is DataTable)
                {
                    oRet[i] = ((DataTable)arr[i]).Copy();

                }
                else
                {
                    oRet[i] = arr[i];

                }
            }

            return oRet;
        }



        #endregion
    }
}
